import time
import pymysql.cursors
import numpy as np
import pymysql
import pandas as pd

class MysqlUtil(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host="127.0.0.3",
            user="root",  # 原代码中是name，应该是user
            password="123456",
            db="customer",
            port=3308,
            charset="utf8"
        )

    def get_data(self):
        cursor= self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql= """
        SELECT t.tourist_agency_name, rel.id_no, LEFT(rel.id_no, 2) as province_code, DAYOFWEEK(gate.create_time) as non_weekend,
        cast(substring(rel.id_no, 7, 4) as unsigned) FROM ticket_order_user_rel rel
        JOIN ticket_order t on t.id = rel.order_id
        JOIN order_user_gate_rel gate on gate.ticket_rel_id = rel.id
        WHERE t.tourist_agency_name != '' and t.pay_time != ''
        """
       
        cursor.execute(sql)
        ret =cursor.fetchall()
        df =pd.DataFrame(ret)
        df['non_weekend'] =df['non_weekend'].apply(lambda x: 1 if x not in [1,7] else 0)

        df['valid_id']=df['id_no'].apply(lambda x:1 if x and str(x).strip() !='' else 0)
        df['elderly_ratio']=df.apply(
            lambda x:1 if (x['valid_id'] and x['unsigned'] >60) else 0 if x['valid_id'] else np.nan,
            axis=1        
        )

        df['out_province_ratio']=df.apply(
            lambda x:1 if (x['valid_id'] and x['province_code'] !='44') else 0 if x['valid_id'] else np.nan,
            axis=1        
        )
        #print(df.head)
        result= df.groupby['tourist_agency_name'].agg(
            total_visitors=('id_no','count'),
            valid_visitors=('valid_id','sum'),
            out_province=('out_province_ratio','sum'),
            elderly=('elderly_ratio','sum'),
            non_weekend_ratio=('non_weekend','mean') 
            )
        
        result['out_province_ratio']=result['out_province'] /result['valid_visitors'].repleace(0,np.nan)
        result['elderly_ratio']=result['elderly'] /result['valid_visitors'].repleace(0,np.nan)
        result=result.drop(['out_province','elderly'],axis=1)
        result['out_province_ratio']=result['out_province_ratio'].fillna(0)
        result['elderly_ratio']=result['elderly_ratio'].fillna(0)  
        result.to_csv('scenic_data.csv')   

if __name__ =="__main__":
    mu=MysqlUtil()
    mn.get_data()